parsing python to csv
- by user185955
I'm trying to download some game stats to do some analysis, only problem is each season the data their isn't 100% consistent.
I grab the json file from the site, then wish to save it to a csv with the first line in the csv containing the heading for that column, so the heading would be essentially the key from the python data type.
#!/usr/bin/env python
import requests
import json
import csv
base_url = 'http://www.afl.com.au/api/cfs/afl/'
token_url = base_url + 'WMCTok'
player_url = base_url + 'matchItems/round'
def printPretty(data):
print(json.dumps(data, sort_keys=True, indent=2, separators=(',', ': ')))
session = requests.Session() # session makes it simple to use the token across the requests
token = session.post(token_url).json()['token'] # get the token
session.headers.update({'X-media-mis-token': token}) # set the token
Season = 2014
Roundno = 4
if Roundno<10:
strRoundno = '0'+str(Roundno)
else:
strRoundno = str(Roundno)
# get some data (could easily be a for loop, might want to put in a delay using Sleep so that you don't get IP blocked)
data = session.get(player_url + '/CD_R'+str(Season)+'014'+strRoundno)
# print everything
printPretty(data.json())
with open('stats_game_test.csv', 'w', newline='') as csvfile:
spamwriter = csv.writer(csvfile, delimiter="'",quotechar='|', quoting=csv.QUOTE_ALL)
for profile in data.json()['items']:
spamwriter.writerow(['%s' %(profile)])
#for key in data.json().keys():
# print("key: %s , value: %s" % (key, data.json()[key]))
The above code grabs the json and writes it to a csv, but it puts the key in each individual cell next to the value (eg 'venueId': 'CD_V190'), the key needs to be just across the first row as a heading.
It gives me a csv file with data in the cells like this
Column A B
'tempInCelsius': 17.0 'totalScore': 32
'tempInCelsius': 16.0 'totalScore': 28
What I want is the data like this
tempInCelsius totalScore
17 32
16 28
As I mentioned up the top, the data isn't always consistent so if I define what fields to grab with spamwriter.writerow([profile['tempInCelsius'], profile['totalScore']]) then it will error out on certain data grabs.
This is why I'm now trying the above method so it just grabs everything regardless of what data is there.
